/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ijournalimporter;

import ijournalimporter.util.DBHelper;
import ijournalimporter.util.ExcelUtil;
import ijournalimporter.util.Util;
import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import javax.swing.JProgressBar;
import javax.swing.JTextArea;

/**
 *
 * @author eric
 */
public class JournalDataImporter {

    private DBHelper helper;
    private File excel;
    private JTextArea output;
    private JProgressBar progressBar;
    private boolean ok = true;
    private HashMap ids = null;
    private int publihserID = 5000;
    private static final int SQL_NULL = java.sql.Types.NULL;
    private static final String now = Util.getTodayWithTime();

    public JournalDataImporter(DBHelper helper, File excel, JTextArea output, JProgressBar progressBar) {
        this.helper = helper;
        this.excel = excel;
        this.output = output;
        this.progressBar = progressBar;

        helper.setAutocommit(false);
    }

    public String doImport() throws Exception {
        // read excel
        ArrayList<ArrayList> rows = ExcelUtil.readExcelToArray(excel, null);
        if (rows == null || rows.isEmpty()) {
            return "讀取Excel檔失敗或無資料！";
        }

        // setup progress bar
        progressBar.setIndeterminate(false);
        progressBar.setMaximum(rows.size());
        progressBar.setStringPainted(true);

        // backup 
        //backup();

        //  validate
        this.readCurrentJournals();
        for (int i = 0; i < rows.size(); i++) {
            ArrayList record = rows.get(i);
            validate(record, i + 1);
        }

        if (!ok) {
            return "資料檢查錯誤，停止匯入！";
        } else {
            this.println("資料驗證OK");
            this.println("開始匯入，共" + rows.size() + "筆");
        }

        insertPublishers(rows);
        PreparedStatement ps = helper.getPreparedStatement("journal", 22);
        for (int i = 0; i < rows.size(); i++) {
            ArrayList record = rows.get(i);
            insert(ps, record, i);
            progressBar.setValue(i);
            Util.delayMS(500);
        }
        ps.executeBatch(); // insert

        println("\r\n匯入成功：" + rows.size() + " 筆");

        return null; // insert OK
    }

    private void backup() {
        helper.excuteUpdate("CREATE TABLE journal_" + now + " SELECT * FROM journal");
        println("backup \"journal\" to \"journal_" + now + "\" ......OK");
        helper.excuteUpdate("CREATE TABLE article_" + now + " SELECT * FROM article");
        println("backup \"article\" to \"article_" + now + "\"......OK");
    }

    private void println(Object msg) {
        StringBuffer sb = new StringBuffer(output.getText());
        sb.append(msg);
        sb.append("\r\n");
        output.setText(sb.toString());
    }

    private void validate(ArrayList<String> record, int no) {
        // check id
        String id = record.get(0);
        if (Util.isBlank(id)) {
            this.println("ERROR: 第" + no + "筆資料的ID為空值");
            ok = false;
        }

        if (ids.containsKey(id)) {
            this.println("ERROR: ID = " + id + " ID重複");
            ok = false;
        } else {
            ids.put(id, null);
        }

        // name
        if (Util.isBlank(record.get(1))) {
            this.println("ERROR: ID = " + id + " 期刊名稱不能為空值");
            ok = false;
        }

        // 中/英出版者都沒有
        if (Util.isBlank(record.get(4)) && Util.isBlank(record.get(5))) {
            this.println("ERROR: ID = " + id + " 中文出版者/英文出版者兩者不能同時為空值");
            ok = false;
        }

    }

    private void readCurrentJournals() throws Exception {
        ids = new HashMap();
        ResultSet rs = helper.executeQuery("SELECT id FROM journal");
        while (rs.next()) {
            ids.put(rs.getString("id"), null);
        }
    }

    private void insert(PreparedStatement ps, ArrayList<String> record, int i) throws SQLException {
        // id
        String id = record.get(0).trim();
        ps.setString(1, id);

        // name
        String name = record.get(1).trim();
        ps.setString(2, name);

        // publisher id
        String publisher = record.get(4);
        if (Util.isBlank(publisher)) {
            publisher = record.get(5);
        }
        String publisherID = this.getPublisherID(publisher);
        ps.setString(3, publisherID);

        // frequency
        String frequency = record.get(3).trim();
        ps.setString(4, frequency);

        // found year
        String foundYear = record.get(8).trim();
        ps.setString(5, foundYear);

        // language
        ps.setString(6, "");

        // issn
        String issn = record.get(9).trim();
        ps.setString(7, issn);

        //embargo
        ps.setNull(8, SQL_NULL);

        // embargoFreq
        ps.setString(9, "");

        // oaLink
        String link = record.get(15);
        if (Util.isNotBlank(link)) {
            ps.setString(10, link.trim());
        } else {
            ps.setString(10, "");
        }

        // picture
        ps.setString(11, "");

        //beginVolume
        ps.setString(12, "");

        //begin Issue
       ps.setString(13, "");

        //authorized
        ps.setString(14, "");

        //currCommission
        ps.setString(15, "");

        //pastCommission
        ps.setString(16, "");

        //fkContractID
        ps.setString(17, "1");

        //note
        String note = record.get(17);
        if (Util.isNotBlank(link)) {
            ps.setString(18, note.trim());
        } else {
            ps.setString(18, "");
        }

        // cid
        ps.setString(19, "IMPORTER");

        // ctime
        ps.setString(20, now);

        // uid
        ps.setNull(21, SQL_NULL);

        // utime
        ps.setNull(22, SQL_NULL);

        ps.addBatch();

        println("ID = " + id + " 匯入成功");
    }

    private void insertPublishers(ArrayList<ArrayList> rows) throws SQLException {
        for (ArrayList<String> record : rows) {
            String publisher = record.get(4);
            if (Util.isBlank(publisher)) {
                publisher = record.get(5);
            }

            String id = this.getPublisherID(publisher);
            if (id == null) {
                helper.excuteUpdate("INSERT INTO publisher (id, name, cid, ctime,contactName,contactPhone,contactEmail,note) VALUES('" + publihserID + "','" + publisher.trim() + "','" + "IMPORTER','" + Util.getTodayWithTime() + "','','','','')");
                publihserID++;
            }
        }
    }

    private String getPublisherID(String name) throws SQLException {
        ResultSet rs = helper.executeQuery("SELECT id FROM publisher WHERE name='" + name + "'");
        if (rs.next()) {
            return rs.getString(1);
        } else {
            return null;
        }

    }
}
